﻿using Model;
using System;
using System.Data;

namespace BLL
{
    public class YX_BankCheckBLL : BaseBLL
    {
        public DataTable GetListByPage(string whereStr, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"SELECT r,
       N_BANKCHECK_ID,
       C_BANKCHECK_NAMEPAYEE,
       USER_NAME,
       to_char(D_BANKCHECK_DATE, 'yyyy-mm-dd') D_BANKCHECK_DATE,
       N_BANKCHECK_MONEY,
       C_BANKCHECK_REMARK,
       N_BANKCHECK_TYPE,
       CMY_NAME,
       ACCOUNTNO,
       C_NAME
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r,
               YXBC.N_BANKCHECK_ID AS N_BANKCHECK_ID,
               YXBC.C_BANKCHECK_NAMEPAYEE AS C_BANKCHECK_NAMEPAYEE,
               SUSER.USER_NAME AS USER_NAME,
               YXBC.D_BANKCHECK_DATE AS D_BANKCHECK_DATE,
               YXBC.N_BANKCHECK_MONEY AS N_BANKCHECK_MONEY,
               YXBC.C_BANKCHECK_REMARK AS C_BANKCHECK_REMARK,
               DECODE(YXBC.N_BANKCHECK_TYPE, 1, '转账', 2, '现金') AS N_BANKCHECK_TYPE,
               SCOM.CMY_NAME AS CMY_NAME,
               YXAC.ACCOUNTNO AS ACCOUNTNO,
               SBAN.C_NAME AS C_NAME
          FROM YX_BANKCHECK YXBC
          LEFT JOIN SYS_USER SUSER
            ON YXBC.N_BANKCHECK_USERID = SUSER.USER_ID
          LEFT JOIN SYS_COMPANY SCOM
            ON SCOM.CMY_ID = SUSER.USER_COMPANY
          LEFT JOIN YX_ACCOUNT YXAC
            ON YXBC.C_BANKCHECK_ACCOUNTNUMBER = YXAC.ACCOUNTNO--SUSER.USER_COMPANY = YXAC.COMPANY_ID
          LEFT JOIN SYS_BANK SBAN
            ON SBAN.N_ID = YXAC.N_BANK_ID
         WHERE --YXBC.N_BANKCHECK_BANKID = SBAN.N_ID
           --AND YXBC.C_BANKCHECK_ACCOUNTNUMBER = YXAC.ACCOUNTNO
            YXAC.ACCOUNTTYPE = 1 ");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @" ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }
            return Query(sql, "", "GetListByPage", "查询队列", "");
        }

        public int GetRecordCount(string whereStr)
        {
            //   string sql = string.Format(@"SELECT COUNT(*)
            // FROM YX_BANKCHECK YXBC
            // LEFT JOIN SYS_USER SUSER
            //   ON YXBC.N_BANKCHECK_USERID = SUSER.USER_ID
            // LEFT JOIN SYS_COMPANY SCOM
            //   ON SCOM.CMY_ID = SUSER.USER_COMPANY
            // LEFT JOIN YX_ACCOUNT YXAC
            //   ON YXBC.C_BANKCHECK_ACCOUNTNUMBER = YXAC.ACCOUNTNO--SUSER.USER_COMPANY = YXAC.COMPANY_ID
            // LEFT JOIN SYS_BANK SBAN
            //   ON SBAN.N_ID = YXAC.N_BANK_ID
            //WHERE YXBC.N_BANKCHECK_BANKID = SBAN.N_ID
            //  --AND YXBC.C_BANKCHECK_ACCOUNTNUMBER = YXAC.ACCOUNTNO
            //  AND YXAC.ACCOUNTTYPE = 1 " + whereStr);

            string sql = string.Format(@"SELECT COUNT(*)
  FROM (SELECT
               YXBC.N_BANKCHECK_ID AS N_BANKCHECK_ID,
               YXBC.C_BANKCHECK_NAMEPAYEE AS C_BANKCHECK_NAMEPAYEE,
               SUSER.USER_NAME AS USER_NAME,
               YXBC.D_BANKCHECK_DATE AS D_BANKCHECK_DATE,
               YXBC.N_BANKCHECK_MONEY AS N_BANKCHECK_MONEY,
               YXBC.C_BANKCHECK_REMARK AS C_BANKCHECK_REMARK,
               DECODE(YXBC.N_BANKCHECK_TYPE, 1, '转账', 2, '现金') AS N_BANKCHECK_TYPE,
               SCOM.CMY_NAME AS CMY_NAME,
               YXAC.ACCOUNTNO AS ACCOUNTNO,
               SBAN.C_NAME AS C_NAME
          FROM YX_BANKCHECK YXBC
          LEFT JOIN SYS_USER SUSER
            ON YXBC.N_BANKCHECK_USERID = SUSER.USER_ID
          LEFT JOIN SYS_COMPANY SCOM
            ON SCOM.CMY_ID = SUSER.USER_COMPANY
          LEFT JOIN YX_ACCOUNT YXAC
            ON YXBC.C_BANKCHECK_ACCOUNTNUMBER = YXAC.ACCOUNTNO--SUSER.USER_COMPANY = YXAC.COMPANY_ID
          LEFT JOIN SYS_BANK SBAN
            ON SBAN.N_ID = YXAC.N_BANK_ID
         WHERE --YXBC.N_BANKCHECK_BANKID = SBAN.N_ID
           --AND YXBC.C_BANKCHECK_ACCOUNTNUMBER = YXAC.ACCOUNTNO
            YXAC.ACCOUNTTYPE = 1 ");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @" ) TT";

            int count = GetCount(sql);

            return count;
        }

        /// <summary>
        /// 根据编号查询银行本票明细
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public YX_BankCheck SelectById(string id)
        {
            string sql = string.Format(@"select * from YX_BankCheck a where a.N_BANKCHECK_ID= " + id);

            DataTable dt = Query(sql);

            YX_BankCheck model = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    model = new YX_BankCheck();
                    model.N_BANKCHECK_ID = Convert.ToDecimal(id);
                    model.N_BANKCHECK_BANKID = decimal.Parse(dr["N_BANKCHECK_BANKID"].ToString());
                    model.N_BANKCHECK_MONEY = decimal.Parse(dr["N_BANKCHECK_MONEY"].ToString());
                    model.C_BANKCHECK_NAMEPAYEE = dr["C_BANKCHECK_NAMEPAYEE"].ToString();
                    model.N_BANKCHECK_USERID = decimal.Parse(dr["N_BANKCHECK_USERID"].ToString());
                    model.N_BANKCHECK_TYPE = decimal.Parse(dr["N_BANKCHECK_TYPE"].ToString());
                    model.C_BANKCHECK_ACCOUNTNUMBER = dr["C_BANKCHECK_ACCOUNTNUMBER"].ToString();
                    model.D_BANKCHECK_DATE = Convert.ToDateTime(dr["D_BANKCHECK_DATE"].ToString());
                    model.C_BANKCHECK_REMARK = dr["C_BANKCHECK_REMARK"].ToString();
                    model.C_BANKCHECK_CODE = dr["C_BANKCHECK_CODE"].ToString();
                    model.N_BANKCHECK_CMYID = decimal.Parse(dr["N_BANKCHECK_CMYID"].ToString());
                }
            }

            return model;
        }

        public int INSERT(YX_BankCheck model, string operName)
        {
            model.N_BANKCHECK_ID = GetSequence("SEQ_N_BANKCHECK_ID");
            string sql = string.Format(@"insert into yx_bankcheck
  (n_bankcheck_id,
   c_bankcheck_namepayee,
   n_bankcheck_userid,
   d_bankcheck_date,
   n_bankcheck_money,
   c_bankcheck_remark,
   n_bankcheck_type,
   n_bankcheck_bankid,
   c_bankcheck_accountnumber,
   N_BANKCHECK_CMYID,
   C_BANKCHECK_CODE)
values
  ({0},
   '{1}',
   {2},
   TO_DATE('{3}','YYYY-MM-DD HH24:MI:SS'),
   {4},
   '{5}',
   {6},
   {7},
   '{8}',{9},'{10}')", model.N_BANKCHECK_ID, model.C_BANKCHECK_NAMEPAYEE, model.N_BANKCHECK_USERID, model.D_BANKCHECK_DATE, model.N_BANKCHECK_MONEY, model.C_BANKCHECK_REMARK, model.N_BANKCHECK_TYPE, model.N_BANKCHECK_BANKID, model.C_BANKCHECK_ACCOUNTNUMBER, model.N_BANKCHECK_CMYID, model.C_BANKCHECK_CODE);

            int count = NoQuery(sql, "新增银行本票", "INSERT", operName);
            return count;
        }

        public int UPDATE(YX_BankCheck model, string operName)
        {
            string sql = string.Format(@"update yx_bankcheck
   set c_bankcheck_namepayee     = '{0}',
       n_bankcheck_userid        = {1},
       d_bankcheck_date          = TO_DATE('{2}','YYYY-MM-DD HH24:MI:SS'),
       n_bankcheck_money         = {3},
       c_bankcheck_remark        = '{4}',
       n_bankcheck_type          = {5},
       n_bankcheck_bankid        = {6},
       c_bankcheck_accountnumber = '{7}',
       N_BANKCHECK_CMYID         ={8},
       C_BANKCHECK_CODE          ='{9}'
 where n_bankcheck_id = {10}", model.C_BANKCHECK_NAMEPAYEE, model.N_BANKCHECK_USERID, model.D_BANKCHECK_DATE, model.N_BANKCHECK_MONEY, model.C_BANKCHECK_REMARK, model.N_BANKCHECK_TYPE, model.N_BANKCHECK_BANKID, model.C_BANKCHECK_ACCOUNTNUMBER, model.N_BANKCHECK_CMYID, model.C_BANKCHECK_CODE, model.N_BANKCHECK_ID);
            int count = NoQuery(sql, operName);
            return count;
        }
    }
}